<? 
ob_start();
session_start();
		if(empty($HTTP_SESSION_VARS['user_login'])){
			echo"
			<tr>
			 <td align=center colspan=3>
			 <br><br><br>".$_SESSION['user_login']."
				Maaf, Anda tidak bisa mengakses halaman ini, Anda harus login terlebih dahulu ... <br><br>
				[ <a href='$path/index.php'>silahkan login</a> ]
			 </td>
			</tr>
			";
			//exit();
		}
		include("../lib/config.php");
		include("../lib/function.php");
		include("../lib/header.php");
		$divisi 	= $HTTP_GET_VARS['divisi'];
		$st			= $HTTP_GET_VARS['st'];
		$stat_kar	= $HTTP_GET_VARS['stat_kar'];
		$jabatan	= $HTTP_GET_VARS['jabatan'];
		$pendidikan	= $HTTP_GET_VARS['pendidikan'];
		$golongan	= $HTTP_GET_VARS['golongan'];
		$bulan1		= $HTTP_GET_VARS['bulan1'];
		$bulan2		= $HTTP_GET_VARS['bulan2'];
		$tahun1		= $HTTP_GET_VARS['tahun1'];
		$tahun2		= $HTTP_GET_VARS['tahun2'];
		$jns_kursus	= $HTTP_GET_VARS['jns_kursus'];
		$excel		= $HTTP_GET_VARS['excel'];

	?>
<title>Laporan Training</title>
<?
	if($excel == "yes"){
	}
	else{
?>
<script type="text/javascript">
function Show_Excel(){
	var divisi 		= document.forms[0].elements['divisi'].value;
	var stat_ker 	= document.forms[0].elements['stat_kar'].value;
	var stat_kar 	= document.forms[0].elements['st'].value;
	var jabatan		= document.forms[0].elements['jabatan'].value;
	var pendidikan 	= document.forms[0].elements['pendidikan'].value;
	var golongan	= document.forms[0].elements['golongan'].value;
	var jns_kursus	= document.forms[0].elements['jns_kursus'].value;
	var tahun1 		= document.forms[0].elements['tahun1'].value;
	var tahun2 		= document.forms[0].elements['tahun2'].value;
	var bulan1		= document.forms[0].elements['bulan1'].value;
	var bulan2	 	= document.forms[0].elements['bulan2'].value;
//	var golongan	= document.forms[0].elements['golongan'].value;
	
	location.href = "<?=$PHP_SELF?>?divisi="+divisi+"&st="+stat_kar+"&excel=yes&stat_kar="+stat_ker+"&pendidikan="+pendidikan+"&jabatan="+jabatan+"&golongan="+golongan+"&jns_kursus="+jns_kursus+"&tahun1="+tahun1+"&tahun2="+tahun2+"&bulan1="+bulan1+"&bulan2="+bulan2+"";
}	
</script>
<link rel="stylesheet" type="text/css" href="../style/style.css" />
 <div style="font-size:14px; border-bottom:solid 1px #000000; font-weight:bold;">Laporan Training</div><br>
<fieldset>
 <legend>Filter Data</legend>
 <form action="<?=$PHP_SELF?>" method="get">
 <table border="0">
  <tr>
   <td>Divisi/Biro/Cabang</td>
   <td><select style='width:350px;' name='divisi'>
	    <option value='all'>--semua--</option>
        <?
		$sq2 = mssql_query("SELECT * FROM VLevelCabang WHERE status='1' ORDER BY uraian ASC",$conn);
		while($rq2 = mssql_fetch_array($sq2)){
			echo"<option value='".$rq2[kode_so]."'";
			if($divisi == $rq2[kode_so]){
				echo" selected";
			}
			echo">$rq2[uraian]</option>";
		}
		?>
        </select></td>
   <td align="right">Jabatan</td>
   <td><select name="jabatan">
	    <option value='all'>--semua--</option>
        <?
		$sag = mssql_query("SELECT * FROM MstJabatan ORDER BY jabatan ASC",$conn);
		while($rag = mssql_fetch_array($sag)){
			echo"<option value='".$rag[kode]."'";
			if($agama == $rag[kode]){
				echo" selected";
			}
			echo">$rag[jabatan]</option>";
		}
		?>
   </select></td>
  </tr>
  <tr>
   <td>Status Kerja</td>
   <td width="300"><select name='stat_kar' style='width:350px;'>
	    <option value='all'>--semua--</option>
        <?
	$sq = mssql_query("SELECT * FROM VStatusKaryawan",$conn);
	while($rq = mssql_fetch_array($sq)){
		echo"<option value='".$rq[kode]."'";
		if($stat_kar == $rq[kode]){
			echo" selected";
		}
		echo">$rq[Nama]</option>";
	}
		?>
        </select></td>
   <td align="right">Pendidikan</td>
   <td><select name="pendidikan">
	    <option value='all'>--semua--</option>
        <?
		$skw = mssql_query("SELECT * FROM MstPendidikan ORDER BY keterangan,jurusan ASC",$conn);
		while($rkw = mssql_fetch_array($skw)){
			echo"<option value='".$rkw[Pend_ID]."'";
			if($marital == $rkw[Pend_ID]){
				echo" selected";
			}
			echo">$rkw[keterangan] - $rkw[jurusan]</option>";
		}
		?>
   </select></td>
   </tr>
   <tr>
   <td>Status Karyawan</td>
   <td><select name="st">
   <option value="all">--semua--</option>
   <option value="bekerja" <? if($st=="bekerja"){ echo" selected"; } ?>>Bekerja</option>
   <option value="berhenti" <? if($st=="berhenti"){ echo" selected"; } ?>>Berhenti</option>
   </select></td>
   <td align="right">Golongan</td>
   <td><select name="golongan">
	    <option value='all'>--semua--</option>
		<?
        	for($i=0;$i<=17;$i++){
				echo"<option value='$i'>$i</option>";
			}
		?>
      </select>
      Jenis Pelatihan <select name="jns_kursus">
      <option value="all">--semua--</option>
      <?
      	$sjp = mssql_query("select * from mstdata where groupcode='kursus'");
		while($rjp = mssql_fetch_array($sjp)){
			echo"<option value='$rjp[Code]'>$rjp[Remarks]</option>";
		}
	  ?>
      </select>
      </td>
  </tr>
  <tr>
   <td>Periode</td>
   <td colspan="3">
   <?
			 echo"<select name='bulan1'>
			  <option value=''";
			  if(empty($bulan1)){ echo" selected"; }
			  echo">--Bulan--</option>";
			  echo"
			   <option value='01'"; if($bulan1 == "01"){ echo" selected"; } else {} echo">Januari</option>
			   <option value='02'"; if($bulan1 == "02"){ echo" selected"; } else {} echo">Februari</option>
			   <option value='03'"; if($bulan1 == "03"){ echo" selected"; } else {} echo">Maret</option>
			   <option value='04'"; if($bulan1 == "04"){ echo" selected"; } else {} echo">April</option>
			   <option value='05'"; if($bulan1 == "05"){ echo" selected"; } else {} echo">Mei</option>
			   <option value='06'"; if($bulan1 == "06"){ echo" selected"; } else {} echo">Juni</option>
			   <option value='07'"; if($bulan1 == "07"){ echo" selected"; } else {} echo">Juli</option>
			   <option value='08'"; if($bulan1 == "08"){ echo" selected"; } else {} echo">Agustus</option>
			   <option value='09'"; if($bulan1 == "09"){ echo" selected"; } else {} echo">September</option>
			   <option value='10'"; if($bulan1 == "10"){ echo" selected"; } else {} echo">Oktober</option>
			   <option value='11'"; if($bulan1 == "11"){ echo" selected"; } else {} echo">November</option>
			   <option value='12'"; if($bulan1 == "12"){ echo" selected"; } else {} echo">Desember</option>
			 </select>
			 <select name='tahun1'>
			  <option value=''>--Tahun--</option>";
				for($t1=1980;$t1<=2100;$t1++){
					echo"<option value='$t1'";
					if($tahun1 == $t1){
						echo" selected";
					}
					echo">$t1</option>";
				}
			  echo"
			 </select>
			 s.d
			 <select name='bulan2'>
			  <option value=''";
			  if(empty($bulan2)){ echo"selected"; }			  
			  echo">--Bulan--</option>
			   <option value='01'"; if($bulan2 == "01"){ echo" selected"; } else {} echo">Januari</option>
			   <option value='02'"; if($bulan2 == "02"){ echo" selected"; } else {} echo">Februari</option>
			   <option value='03'"; if($bulan2 == "03"){ echo" selected"; } else {} echo">Maret</option>
			   <option value='04'"; if($bulan2 == "04"){ echo" selected"; } else {} echo">April</option>
			   <option value='05'"; if($bulan2 == "05"){ echo" selected"; } else {} echo">Mei</option>
			   <option value='06'"; if($bulan2 == "06"){ echo" selected"; } else {} echo">Juni</option>
			   <option value='07'"; if($bulan2 == "07"){ echo" selected"; } else {} echo">Juli</option>
			   <option value='08'"; if($bulan2 == "08"){ echo" selected"; } else {} echo">Agustus</option>
			   <option value='09'"; if($bulan2 == "09"){ echo" selected"; } else {} echo">September</option>
			   <option value='10'"; if($bulan2 == "10"){ echo" selected"; } else {} echo">Oktober</option>
			   <option value='11'"; if($bulan2 == "11"){ echo" selected"; } else {} echo">November</option>
			   <option value='12'"; if($bulan2 == "12"){ echo" selected"; } else {} echo">Desember</option>
			 </select>
			 <select name='tahun2'>
			  <option value=''>--Tahun--</option>";
				for($t2=1980;$t2<=2100;$t2++){
					echo"<option value='$t2'";
					if($tahun2 == $t2){
						echo" selected";
					}
					echo">$t2</option>";
				}
			  echo"</select>";
			  ?>
   </td>
  </tr>
  <tr>
   <td>&nbsp;</td>
   <td><input type="submit" class="button" value="Cari"><input type="button" value="Kirim ke Excel" onclick="Javascript:Show_Excel();" class="button" /></td>
  </tr>
 </table>
 </form> 
</fieldset>
<?
// end if xecle
}

	$sqlcount = "SELECT A.NIK,program,Pend_ID, kode_so2, jabatan2,fungsi2, stat_kar2, A.jenis, gol2, C.dari, C.sampai, C.jenis, C.tempat, C.penyelenggara, C.biayatraining, C.biayaakomodasi, C.biayatransport, C.biayalain from vlatestkarir A, Pendidikan B, Kursus C where A.NIK=B.NIK AND A.NIK=C.NIK ";
	
	// status kerja
	if(!empty($st)){
		if($st == "all"){
		
		}
		else{
			if($st == "berhenti"){
				$sqlcount .= " AND A.Jenis IN ('U','H') ";			
			}
			elseif($st == "bekerja"){
				$sqlcount .= " AND A.Jenis NOT IN ('U','H') ";
			}
		}
	}
	// golongan
		if($golongan == "all" || $golongan == NULL){
		
		}
		else{
			$sqlcount .= " AND gol2='$golongan'";
		}

	// Jenis Kursus
		if($jns_kursus == "all" || $jns_kursus == NULL){
		
		}
		else{
			$sqlcount .= " AND C.jenis='$jns_kursus'";
		}
	// jabatan
		if($jabatan == "all" || $jns_kursus == NULL){
		
		}
		else{
			$sqlcount .= " AND jabatan2='$jabatan'";
		}
	// pendidikan
		if($pendidikan == "all" || $pendidikan == NULL){
		
		}
		else{
			$sqlcount .= " AND Pend_ID='$pendidikan'";
		}
	
	// bulan dan tahun kursus
	if($bulan2+1 == 13){
		$bulanke2 = "01";
	}
	else{
		$bulanke2 = ($bulan2+1);
	}

	if($bulan1+1 == 13){
		$bulanke1 = "01";
	}
	else{
		$bulanke1 = ($bulan1+1);
	}
	
	  // cek bulan dan tahun
	 if(!empty($bulan1) && !empty($tahun1) && !empty($bulan2) && !empty($tahun2)){
		$sqlcount .= " and C.dari between convert(datetime,'".$bulan1."/01/".$tahun1."') and convert(datetime,'".$bulanke2."/01/".$HTTP_GET_VARS['tahun2']."')";
	 }
	 else{	
	 	 
		 if(!empty($bulan1) && !empty($tahun1) && empty($bulan2) && empty($tahun2)){
			$sqlcount .= " and C.dari between convert(datetime,'".$bulan1."/01/".$tahun1."') and convert(datetime,'".$bulanke1."/01/".$tahun1."') ";
		 }
		 elseif(!empty($bulan2) && !empty($tahun2) && empty($bulan1) && empty($tahun1)){
			$sqlcount .= " and C.dari between convert(datetime,'".$bulan2."/01/".$tahun2."') and convert(datetime,'".$bulanke2."/01/".$tahun2."') ";
		 }	
	}
	
	////////////////////////////////////////
		
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sqlcount .= " AND substring(kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
	
	if(!empty($stat_kar)){
		if($stat_kar == "all"){		
		}
		else{
			$sqlcount .= " AND stat_kar2='$stat_kar' ";
		}
	}
	
	$sqlcount .= " group by program, A.NIK, Pend_ID, kode_so2,jabatan2,fungsi2, stat_kar2, A.jenis, gol2, C.dari, C.sampai, C.jenis, C.tempat, C.penyelenggara, C.biayatraining, C.biayaakomodasi, C.biayatransport, C.biayalain ";
	
	//echo $sqlcount;
$result	= mssql_query($sqlcount,$conn);
$total	= mssql_num_rows($result);
$limit	= 20;
$pager  = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit  = $pager->limit;
$page   = $pager->page;

if($total > 0)
{
	$sql = "WITH AAA AS(SELECT ROW_NUMBER() OVER (ORDER BY A.NIK) AS No, A.Nama, A.NIK,program,Pend_ID, kode_so2, jabatan2,fungsi2, stat_kar2, A.jenis jenis_surat, gol2, CONVERT(CHAR(12),C.dari,103) tgl_dari, CONVERT(CHAR(12), C.sampai, 103) tgl_sampai, C.jenis, C.tempat, C.penyelenggara, C.biayatraining, C.biayaakomodasi, C.biayatransport, C.biayalain from vlatestkarir A, Pendidikan B, Kursus C where A.NIK=B.NIK AND A.NIK=C.NIK  ";
	
	// status kerja
	if(!empty($st)){
		if($st == "all"){
		
		}
		else{
			if($st == "berhenti"){
				$sql .= " AND A.jenis IN ('U','H') ";			
			}
			elseif($st == "bekerja"){
				$sql .= " AND A.jenis NOT IN ('U','H') ";
			}
		}
	}
	// golongan
		if($golongan == "all" || $golongan == NULL){
		
		}
		else{
			$sql .= " AND gol2='$golongan'";
		}

	// Jenis Kursus
		if($jns_kursus == "all" || $jns_kursus == NULL){
		
		}
		else{
			$sql .= " AND C.jenis='$jns_kursus'";
		}
	// jabatan
		if($jabatan == "all" || $jabatan == NULL){
		
		}
		else{
			$sql .= " AND jabatan2='$jabatan'";
		}
	// pendidikan
		if($pendidikan == "all" || $pendidikan == NULL){
		
		}
		else{
			$sql .= " AND Pend_ID='$pendidikan'";
		}
	
	// bulan dan tahun kursus
	if($bulan2+1 == 13){
		$bulanke2 = "01";
	}
	else{
		$bulanke2 = ($bulan2+1);
	}

	if($bulan1+1 == 13){
		$bulanke1 = "01";
	}
	else{
		$bulanke1 = ($bulan1+1);
	}
	
	  // cek bulan dan tahun
	 if(!empty($bulan1) && !empty($tahun1) && !empty($bulan2) && !empty($tahun2)){
		$sql .= " and C.dari between convert(datetime,'".$bulan1."/01/".$tahun1."') and convert(datetime,'".$bulanke2."/01/".$HTTP_GET_VARS['tahun2']."')";
	 }
	 else{	
	 	 
		 if(!empty($bulan1) && !empty($tahun1) && empty($bulan2) && empty($tahun2)){
			$sql .= " and C.dari between convert(datetime,'".$bulan1."/01/".$tahun1."') and convert(datetime,'".$bulanke1."/01/".$tahun1."') ";
		 }
		 elseif(!empty($bulan2) && !empty($tahun2) && empty($bulan1) && empty($tahun1)){
			$sql .= " and C.dari between convert(datetime,'".$bulan2."/01/".$tahun2."') and convert(datetime,'".$bulanke2."/01/".$tahun2."') ";
		 }	
	}
	
	////////////////////////////////////////
		
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sql .= " AND substring(kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
	
	if(!empty($stat_kar)){
		if($stat_kar == "all"){		
		}
		else{
			$sql .= " AND stat_kar2='$stat_kar' ";
		}
	}
	
	$sql .= " group by program, A.NIK, A.Nama, Pend_ID, kode_so2,jabatan2,fungsi2, stat_kar2, A.jenis, gol2, C.dari, C.sampai, C.jenis, C.tempat, C.penyelenggara, C.biayatraining, C.biayaakomodasi, C.biayatransport, C.biayalain ";
	
	$sql .= ") SELECT * FROM AAA ";
	
	if($excel == "yes"){
	
	}
	else{
		$sql .= " WHERE No BETWEEN ".$offset." AND ".($offset+$limit)."";
	}
	
	$query = mssql_query($sql,$conn);
	
	if($excel == "yes"){
	
	}
	else{
		PagingLapTr($pager,$page,$next,$limit,$keyword,$show,$conn,$st,$divisi,$stat_kar,$pendidikan,$jabatan,$golongan,$bulan1,$bulan2,$tahun2,$tahun1,$jns_kursus);
	}
	
	if($excel == "yes"){
	
	}
	else{
?>
<fieldset>
 <legend>Hasil Pencarian</legend>
<?
	}
?>
 <table width="100%">
  <tr class="headlist">
   <td>No.</td>
   <td>NIK</td>
   <td>Nama</td>
   <td>Divisi/Biro/Cabang</td>
   <td>Jabatan</td>
   <td>Fungsional</td>
   <td>Status Karyawan</td>
   <td>Status Kerja</td>
   <td>Tgl. Masuk</td>
   <td>Program</td>
   <td>Penyelenggara</td>
   <td>Tempat</td>
   <td>Dari</td>
   <td>Sampai</td>
   <td>Biaya Training</td>
   <td>Biaya Akomodasi</td>
   <td>Biaya Transport</td>
   <td>Biaya Lain-Lain</td>
  </tr>
  <?
  while($row = mssql_fetch_array($query)){
  ?>
  <tr class="contentlist" style="font-size:smaller;">
   <td align="right"><?=$row[No]?></td>
   <td><?=$row[NIK]?></td>
   <td><?=$row[Nama]?></td>
   <td>
   <?
   // get divisi
	$kode_so = $row[kode_so2];
	if(empty($kode_so)){
		
	}
	else{
		$sdiv = mssql_query("SELECT uraian FROM VLevelCabang WHERE substring(kode_so,1,7)='".substr($kode_so,0,7)."'");
		$rdiv = mssql_fetch_array($sdiv);
		echo"$rdiv[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get jabatan
   	if(empty($row[jabatan2])){
	
	}
	else{
		$jabatan = $row[jabatan2];
		$sjab	= mssql_query("SELECT Jabatan FROM MstJabatan WHERE kode='$jabatan'",$conn);
		$rjab	= mssql_fetch_array($sjab);
		echo"$rjab[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get fungsional
   	if(empty($row[fungsi2])){
	
	}
	else{
		$fungsi = $row[fungsi2];
		$sfun	= mssql_query("SELECT Nama FROM MstFungsional WHERE kode='$fungsi'",$conn);
		$rfun	= mssql_fetch_array($sfun);
		echo"$rfun[0]";
	}
   ?>
   </td>
   <td>
   <?
   // status karyawan
   	if(empty($row[stat_kar2])){
	
	}
	else{
		$stat_kar2 = $row[stat_kar2];
		$sstat_kar2	= mssql_query("SELECT Nama FROM VStatusKaryawan WHERE kode='$stat_kar2'",$conn);
		$rstat_kar2	= mssql_fetch_array($sstat_kar2);
		echo"$rstat_kar2[0]";
	}
   ?>
   </td>
   <td>
    <?
    // status kerja
	if($row[jenis_surat] == "U" || $row[jenis_surat] == "H"){
		echo"Berhenti";
	}
	else{
		echo"Bekerja";
	}
	?>
   </td>
   <td>
   <?
   // tgl masuk
   $stglmasuk = mssql_query("SELECT CONVERT(CHAR(12), tgl_efektif, 103) tgl, year(tgl_efektif) lama_kerja FROM VFirstKarir WHERE NIK='$row[NIK]'",$conn);
   $rtglmasuk = mssql_fetch_array($stglmasuk);
   echo"$rtglmasuk[tgl]";
   ?>
   </td>
   <td><?=$row[program]?></td>
   <td><?=$row[penyelenggara]?></td>
   <td><?=$row[tempat]?></td>
   <td><?=$row[tgl_dari]?></td>
   <td><?=$row[tgl_sampai]?></td>
   <td align="right"><?=$row[biayatraining]?></td>
   <td align="right"><?=$row[biayaakomodasi]?></td>
   <td align="right"><?=$row[biayatransport]?></td>
   <td align="right"><?=$row[biayalain]?></td>
  </tr>
  <?
  }
  ?>
 </table>
 <?
 	if($excel == "yes"){
		header('Content-type: application/vnd.ms-excel');
		header("Content-Disposition: attachment; filename=LaporanTraining".date("dmY").".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
	}
	else{
 ?>
</fieldset>
<?
	}
}
?>